import mysql.connector
import xlrd
import datetime

xls_file = r'D:\demo\雨量数据.xlsx'
workbook = xlrd.open_workbook(xls_file)
# 获取sheet数量
count_sheet = len(workbook.sheets())
count_rows = workbook.sheet_by_index(0).nrows - 1
# 连接数据库
conn = mysql.connector.connect(
    user='root',
    password='summit-1',
    host='192.168.1.100',
    port=3306,
    database='dt',
    charset='utf8')
cursor = conn.cursor()
start_time = datetime.datetime.now()
print('开始时间：{}'.format(start_time))

for i in range(0, count_sheet):
    sheet = workbook.sheet_by_index(i)

    query = """
    INSERT INTO st_pptn_r (addvcdId, stationId, TM, DYP) VALUE
    """
    for r in range(1, sheet.nrows):
        addvcdId = sheet.cell(r, 0).value
        stationId = sheet.cell(r, 1).value
        tm = sheet.cell(r, 3).value
        dyp = sheet.cell(r, 4).value

        value = (addvcdId, stationId, tm, dyp)
        query_m = query + str(value)
        cursor.execute(query_m)

cursor.close()
conn.commit()
conn.close()
end_time = datetime.datetime.now()
print('结束时间：{}'.format(end_time))
print('用时：{}秒'.format(end_time - start_time))
print('完成{}条数据'.format(count_rows))
